-- common..p_InsItem 0,'P','IOPU','VT','AA','BLR','LOT1',10.0,10.0,10.0,200.0,10.0,10.0,0.0,'SUNNY','05/03/2011',240.0,'CMV'
use common
go
IF OBJECT_ID('dbo.p_DelItem') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.p_DelItem
    IF OBJECT_ID('dbo.p_DelItem') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.p_DelItem >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.p_DelItem >>>'
END
go
create proc p_DelItem
(
	@ItemCode			varchar(50)	-- ItemCode
	
) as
begin
	-- declare

    declare @DateUpdated	datetime	-- datetime when this row was updated
    declare @UserUpdated	varchar(15)	-- user name that updated this row
	declare @Msg 		varchar(255)	-- last error message
	declare @ErrVal		int		-- last error number
	declare @ItemId		int	
	-- initialize
	select @ErrVal=0
	select @DateUpdated=getdate()
	select @UserUpdated=suser_name()
	select @ItemId=0
	begin tran p_DelItem
		select @ErrVal=0
		if @ErrVal=0
		begin
  print 'insert started'
				
				delete from  common..t_ItemMaster where ItemCode = @ItemCode
	
				select @ErrVal=@@error
			
		end
		if @ErrVal!=0
		begin
			rollback tran p_DelItem
			select @ItemId=0
			select @ErrVal=99999,@Msg="Failed to delete:" + @ItemCode
			goto ErrorHandler
		end
	commit tran p_DelItem
    --end

	if @ErrVal!=0
	begin
			select @ErrVal=99999, @Msg="Failed to delete ItemCode:" + @ItemCode
			goto ErrorHandler
	end
Done:

	return 0 

ErrorHandler:
	raiserror @ErrVal @Msg
end
go
EXEC sp_procxmode 'dbo.p_DelItem','unchained'
go
IF OBJECT_ID('dbo.p_DelItem') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.p_DelItem >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.p_DelItem >>>'
go
GRANT EXECUTE ON dbo.p_DelItem TO public
go